package com.mojian.mapper;

import com.mojian.entity.SysTb;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 投标 Mapper接口
 */
@Mapper
public interface SysTbMapper extends BaseMapper<SysTb> {

    @Select("SELECT tb.id,tb.user_id,tb.zbbt,tb.zbid,tb.sfje,tb.tbwj,tb.status,DATE_FORMAT(tb.create_time,'%Y-%m-%d %H:%i:%s') create_time,tb.update_time,tb.a1,tb.a2,tb.a3,tb.a4,tb.a5,tb.a6,tb.a7,tb.a8,tb.a9,tb.a10, " +
            "user.nickname as gysmc, user.username as gysbh, " +
            "CASE WHEN user.pj = 'A' THEN tb.sfje * 0.8 " +
            "WHEN user.pj = 'B' THEN tb.sfje * 0.85 " +
            "WHEN user.pj = 'C' THEN tb.sfje * 0.9 " +
            "WHEN user.pj = 'D' THEN tb.sfje * 0.95 " +
            "ELSE tb.sfje END AS DF " +
            "FROM sys_tb tb " +
            "LEFT JOIN (SELECT id as userid, pj, nickname, username FROM sys_user) user ON tb.user_id = user.userid " +
            "WHERE tb.zbid = #{zbid} " +
            "ORDER BY CASE WHEN user.pj = 'A' THEN tb.sfje * 0.8 " +
            "WHEN user.pj = 'B' THEN tb.sfje * 0.85 " +
            "WHEN user.pj = 'C' THEN tb.sfje * 0.9 " +
            "WHEN user.pj = 'D' THEN tb.sfje * 0.95 " +
            "ELSE tb.sfje * 1 END")
    List<Map<String, Object>> selectCustomList(@Param("zbid") String zbid);

    @Select("SELECT tb.zbid,tb.zbbt, user.nickname as gysmc, user.username as gysbh,tb.a1 as dbrq " +
            "FROM sys_tb tb " +
            "LEFT JOIN (SELECT id as userid, pj, nickname, username FROM sys_user) user " +
            "ON tb.user_id = user.userid " +
            "WHERE tb.status = '1'")
    List<Map<String, Object>> selectTbUserInfo();

    @Select("select '待激活' lx,count(1) sl from sys_zb where status='0' " +
            "union all " +
            "select '进行中',count(1) from sys_zb where status='1' " +
            "union all  " +
            "select '归档',count(1) from sys_zb where status='2' ")
    List<Map<String, Object>> selectYBPSJ();


}
